use rusqlite::{params, Connection, ToSql};
use tauri::State;

use crate::error::error::MyError;
use crate::model::db_connection::DatabaseConnection;
use crate::model::material::*;
use crate::model::output::Output;

#[tauri::command(rename_all = "snake_case")]
pub fn create_material(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Material>, MyError> {
    let mut material: Material = serde_json::from_str(data)
        .map_err(|e| MyError::new(1000, "Parse JSON error".to_string(), e.to_string()))?;

    // 设置创建时间和修改时间
    let now = chrono::Local::now().naive_local();
    material.created_at = now;
    material.modified_at = now;

    // 设置创建者和修改者（这里需要根据实际业务获取当前用户）
    material.created_by = "system".to_string(); // 替换为实际用户
    material.modified_by = "system".to_string(); // 替换为实际用户

    let result = state
        .conn
        .lock()
        .unwrap()
        .execute(
            r#"INSERT INTO material (
                name, code, parent_id, supplier_id, color_code, background_material,
                status, length, width, thickness, residual, price, unit, remark,
                created_by, created_at, modified_by, modified_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"#,
            params![
                material.name,
                material.code,
                material.parent_id,
                material.supplier_id,
                material.color_code,
                material.background_material,
                material.status,
                material.length,
                material.width,
                material.thickness,
                material.residual,
                material.price,
                material.unit,
                material.remark,
                material.created_by,
                material.created_at,
                material.modified_by,
                material.modified_at
            ],
        )
        .map_err(|e| MyError::new(1002, "Insert material error".to_string(), e.to_string()))?;

    // 获取最后插入的ID
    let id = state.conn.lock().unwrap().last_insert_rowid() as i32;
    material.id = Some(id);

    Ok(Output::success(
        "Material created successfully".to_string(),
        Some(material),
    ))
}

#[tauri::command(rename_all = "snake_case")]
pub fn update_material(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Material>, MyError> {
    let material: Material = serde_json::from_str(data)
        .map_err(|e| MyError::new(1000, "Parse JSON error".to_string(), e.to_string()))?;

    let sql = r#"
        UPDATE material
        SET
            name = ?1,
            code = ?2,
            parent_id = ?3,
            supplier_id = ?4,
            color_code = ?5,
            background_material = ?6,
            status = ?7,
            length = ?8,
            width = ?9,
            thickness = ?10,
            residual = ?11,
            price = ?12,
            unit = ?13,
            remark = ?14,
            created_by = ?15,
            created_at = ?16,
            modified_by = ?17,
            modified_at = ?18
        WHERE id = ?19
    "#;

    let conn = state.conn.lock().unwrap();
    let affected_rows = conn
        .execute(
            sql,
            params![
                material.name,
                material.code,
                material.parent_id,
                material.supplier_id,
                material.color_code,
                material.background_material,
                material.status,
                material.length,
                material.width,
                material.thickness,
                material.residual,
                material.price,
                material.unit,
                material.remark,
                material.created_by,
                material.created_at,
                material.modified_by,
                material.modified_at,
                material.id,
            ],
        )
        .map_err(|e| {
            MyError::database_error("Update material failed".to_string(), e.to_string())
        })?;

    if affected_rows == 0 {
        return Err(MyError::new(
            404,
            "Material not found".to_string(),
            "".to_string(),
        ));
    }

    // 返回更新后的数据（可选）
    Ok(Output::success(
        "Update successful".to_string(),
        Some(material),
    ))
}
/**
 * 查询材料列表， 如果顶级材料parent_material_id应该传递0
 */
#[tauri::command(rename_all = "snake_case")]
pub fn list_material(
    material_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Vec<Material>>, MyError> {
    //查询

    let (sql, params): (&str, Vec<&dyn ToSql>) = (
        r#"SELECT id, name, code, parent_id, supplier_id, color_code, background_material,
                      status, length, width, thickness, residual, price, unit,remark,
                      created_by, created_at, modified_by, modified_at
               FROM material WHERE status = 1 AND parent_id = ?1"#,
        vec![&material_id],
    );

    let conn = state.conn.lock().unwrap();
    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in list_material".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*params, |row| {
            Ok(Material {
                id: row.get(0)?,
                name: row.get(1)?,
                code: row.get(2)?,
                parent_id: row.get(3)?,
                supplier_id: row.get(4)?,
                color_code: row.get(5)?,
                background_material: row.get(6)?,
                status: row.get(7)?,
                length: row.get(8)?,
                width: row.get(9)?,
                thickness: row.get(10)?,
                residual: row.get(11)?,
                price: row.get(12)?,
                unit: row.get(13)?,
                remark: row.get(14)?,
                created_by: row.get(15)?,
                created_at: row.get(16)?,
                modified_by: row.get(17)?,
                modified_at: row.get(18)?,
                is_leaf: None,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_material".to_string(),
                e.to_string(),
            )
        })?;

    let mut material_list = Vec::new();
    for row in rows {
        let material = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_material".to_string(),
                e.to_string(),
            )
        })?;
        material_list.push(material);
    }

    //空，说明时叶子节点
    if material_list.is_empty() {
        let material_output = detail(material_id, &conn)?.data.ok_or_else(|| {
            MyError::custom_error(
                10000,
                "No material found".to_string(),
                "No material found".to_string(),
            )
        });
        let mut material = material_output?;
        material.is_leaf = Some(true);
        material_list.push(material);
    }

    Ok(Output::success("OK".to_string(), Some(material_list)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_materials_by_code(
    material_code: Option<String>,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Vec<Material>>, MyError> {
    let (sql, params): (&str, Vec<&dyn ToSql>) = (
        r#"SELECT id, name, code, parent_id, supplier_id, color_code, background_material,
                      status, length, width, thickness, residual, price, unit,remark,
                      created_by, created_at, modified_by, modified_at
               FROM material WHERE status = 1 AND code  LIKE '%' || ?1 || '%'"#,
        vec![&material_code],
    );

    let conn = state.conn.lock().unwrap();
    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in list_material".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*params, |row| {
            Ok(Material {
                id: row.get(0)?,
                name: row.get(1)?,
                code: row.get(2)?,
                parent_id: row.get(3)?,
                supplier_id: row.get(4)?,
                color_code: row.get(5)?,
                background_material: row.get(6)?,
                status: row.get(7)?,
                length: row.get(8)?,
                width: row.get(9)?,
                thickness: row.get(10)?,
                residual: row.get(11)?,
                price: row.get(12)?,
                unit: row.get(13)?,
                remark: row.get(14)?,
                created_by: row.get(15)?,
                created_at: row.get(16)?,
                modified_by: row.get(17)?,
                modified_at: row.get(18)?,
                is_leaf: None,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_material".to_string(),
                e.to_string(),
            )
        })?;

    let mut material_list = Vec::new();
    for row in rows {
        let material = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_material".to_string(),
                e.to_string(),
            )
        })?;
        material_list.push(material);
    }
    Ok(Output::success("OK".to_string(), Some(material_list)))
}

fn detail(material_id: i32, conn: &Connection) -> Result<Output<Material>, MyError> {
    let sql = "SELECT id, name, code, parent_id, supplier_id, color_code, background_material,
        status, length, width, thickness, residual, price, unit, remark, created_by, created_at,
        modified_by, modified_at FROM material WHERE id = ?";

    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in material_detail".to_string(),
            e.to_string(),
        )
    })?;

    let row = stmt
        .query_row(params![material_id], |row| {
            Ok(Material {
                id: row.get(0)?,
                name: row.get(1)?,
                code: row.get(2)?,
                parent_id: row.get(3)?,
                supplier_id: row.get(4)?,
                color_code: row.get(5)?,
                background_material: row.get(6)?,
                status: row.get(7)?,
                length: row.get(8)?,
                width: row.get(9)?,
                thickness: row.get(10)?,
                residual: row.get(11)?,
                price: row.get(12)?,
                unit: row.get(13)?,
                remark: row.get(14)?,
                created_by: row.get(15)?,
                created_at: row.get(16)?,
                modified_by: row.get(17)?,
                modified_at: row.get(18)?,
                is_leaf: None,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query row error in material_detail".to_string(),
                e.to_string(),
            )
        })?;

    Ok(Output::success("OK".to_string(), Some(row)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn material_detail(
    material_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Material>, MyError> {
    let conn = state.conn.lock().unwrap();
    detail(material_id, &conn)
}
